﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using Infrastructure;
using MSIACCSL2.Web.GroupServices;
using MSIACCSL2.Web.ItemServices;
using MSIACCSL2.Web.SubgroupServices;
using MSIACCSL2.Web.WarehouseServices;

namespace MSIACCSL2.Web.ReportPage
{
    public partial class LaporanKartuStok : System.Web.UI.Page
    {
        private DataAccess<Object> dataAccess;
        private ReportDocument customerReport;

        public LaporanKartuStok()
        {
            dataAccess = new DataAccess<object>();
        }
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Prepare();
            }
            else
            {
                LoadReport((DataTable)Cache["dt"]);
            }
        }

        void Prepare()
        {
            var groupClient = new GroupServicesClient();
            var itemClient = new ItemServicesClient();
            var subgroupClient = new SubGroupServiceClient();
            var warehouseClient = new WarehouseServicesClient();

            var groups = groupClient.GetAll();
            cboGroup.Items.Clear();
            cboGroup.Items.Add(new ListItem("All",""));
            foreach (var groupDto in groups)
            {
                ListItem item = new ListItem(groupDto.GroupName, groupDto.GroupID);
                cboGroup.Items.Add(item);
            }

            var subgroups = subgroupClient.GetAllSubGroup();
            cboSubGroup.Items.Clear();
            cboSubGroup.Items.Add(new ListItem("All",""));
            foreach (var subgroup in subgroups)
            {
                ListItem item = new ListItem(subgroup.SubGroupName, subgroup.SubGroupID);
                cboSubGroup.Items.Add(item);
            }

            var items = itemClient.GetAllItem();
            cboItem.Items.Clear();
            cboItem.Items.Add(new ListItem("All",""));
            foreach (var ite in items)
            {
                ListItem item = new ListItem(ite.ItemName, ite.ItemId);
                cboItem.Items.Add(item);
            }

            var warehouses = warehouseClient.GetAllWarehouse();
            cboWarehouse.Items.Clear();
            cboWarehouse.Items.Add(new ListItem("All",""));
            foreach (var warehouse in warehouses)
            {
                var item = new ListItem(warehouse.WarehouseName, warehouse.KodeWarehouse);
                cboWarehouse.Items.Add(item);
            }
        }

        protected void btnShow_Click(object sender, EventArgs e)
        {
            LoadReport(null);
        }

        private void LoadReport(DataTable source)
        {
            StringBuilder sb = new StringBuilder();

            if (string.IsNullOrEmpty(cboWarehouse.SelectedValue))
            {
                sb.Append("Select * from ( ");
                sb.Append(" SELECT ");
                sb.Append(" i.SerialNumber,b.ItemId,b.ItemName,c.SubGroupId,c.SubGroupName,d.GroupId,d.GroupName,    ");
                sb.Append(" i.NoBundle,i.QtyPack,i.Panjang,i.Lebar,Coalesce(a.SheetSisa,0) as Sheet, ");
                sb.Append(" COALESCE(a.QtySisa,0) as QtyUnit,i.SatuanUnit,i.UnitPrice,COALESCE(a.QtySisa,0) * i.UnitPrice AS Amount, ");
                sb.Append(" w.WarehouseName,i.KodePallet,i.KodeMasuk,i.[Description],i.IsStock ");
                sb.Append(" from Inventory i    ");
                sb.Append(" LEFT JOIN    ");
                sb.Append(" (     ");
                sb.Append(" Select  (QtySisa) AS QtySisa,(SerialNumber) AS SerialNumber,  ");
                sb.Append(" (SheetSisa) AS  SheetSisa,(m.KodeWarehouse) AS KodeWarehouse,  ");
                sb.Append(" RANK() OVER (PARTITION BY SerialNumber,KodeWarehouse ORDER BY TglMutasi DESC) AS Ranking ");
                sb.Append(" from MutasiStok m  Where [TYPE]<>'MutasiWarehouse'   ");
                sb.Append(" and CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, TglMutasi)))<='" + Convert.ToDateTime(txtTgl1.Text, new System.Globalization.CultureInfo("id-ID")).ToString("yyyy-MM-dd") + "'");
                sb.Append(" ) a on a.SerialNumber = i.SerialNumber AND a.Ranking=1 ");
                sb.Append(" LEFT JOIN  Item b on i.ItemID = b.ItemId      ");
                sb.Append(" LEFT JOIN  Subgroup c on b.SubgroupId = c.SubgroupId     ");
                sb.Append(" LEFT JOIN  Groups d on c.GroupId = d.GroupId   ");
                sb.Append(" INNER JOIN (Select SerialNumber,KodeWarehouse,Pallet AS KodePallet from uvw_mutasi_stok  WHERE uvw_mutasi_stok.QtySisa>0) iw ON i.SerialNumber = iw.SerialNumber AND iw.KodeWarehouse=a.KodeWarehouse ");
                sb.Append(" LEFT JOIN Warehouse w ON iw.KodeWarehouse = w.KodeWarehouse  ");
                sb.Append(" ) T ");
                sb.Append(" WHERE IsStock=1  and QtyUnit>0  ");
            }
            else
            {
                sb.Append("Select * from ( ");
                sb.Append(" SELECT ");
                sb.Append(" i.SerialNumber,b.ItemId,b.ItemName,c.SubGroupId,c.SubGroupName,d.GroupId,d.GroupName,    ");
                sb.Append(" i.NoBundle,i.QtyPack,i.Panjang,i.Lebar,Coalesce(a.SheetSisa,0) as Sheet, ");
                sb.Append(" COALESCE(a.QtySisa,0) as QtyUnit,i.SatuanUnit,i.UnitPrice,COALESCE(a.QtySisa,0) * i.UnitPrice AS Amount, ");
                sb.Append(" w.WarehouseName,i.KodePallet,i.KodeMasuk,i.[Description],i.IsStock ");
                sb.Append(" from Inventory i    ");
                sb.Append(" LEFT JOIN    ");
                sb.Append(" (     ");
                sb.Append(" Select  (m.QtySisa) AS QtySisa,(SerialNumber) AS SerialNumber,  ");
                sb.Append(" (m.SheetSisa) AS  SheetSisa,(m.KodeWarehouse) AS KodeWarehouse,  ");
                sb.Append(" RANK() OVER (PARTITION BY SerialNumber,KodeWarehouse ORDER BY TglMutasi DESC) AS Ranking ");
                sb.Append(" from MutasiStok m  Where   ");
                sb.Append(" CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, TglMutasi)))<='" + Convert.ToDateTime(txtTgl1.Text, new System.Globalization.CultureInfo("id-ID")).ToString("yyyy-MM-dd") + "'");
                sb.Append(" And KodeWarehouse='" + cboWarehouse.SelectedValue + "'");
                sb.Append(" ) a on a.SerialNumber = i.SerialNumber  AND a.Ranking=1     ");
                sb.Append(" LEFT JOIN  Item b on i.ItemID = b.ItemId      ");
                sb.Append(" LEFT JOIN  Subgroup c on b.SubgroupId = c.SubgroupId     ");
                sb.Append(" LEFT JOIN  Groups d on c.GroupId = d.GroupId   ");
                sb.Append(" LEFT JOIN (Select SerialNumber,KodeWarehouse,Pallet AS KodePallet from uvw_mutasi_stok) iw ON i.SerialNumber = iw.SerialNumber  ");
                sb.Append(" INNER JOIN Warehouse w ON iw.KodeWarehouse = w.KodeWarehouse AND iw.KodeWarehouse=a.KodeWarehouse ");
                sb.Append(" ) T ");
                sb.Append(" WHERE IsStock=1 and QtyUnit>0  ");
            }

            if (!string.IsNullOrEmpty(txtNoLog.Text))
                sb.AppendFormat(" AND SerialNumber like'persen{0}persen'", txtNoLog.Text);
            if (!string.IsNullOrEmpty(txtKodeMasuk.Text))
                sb.AppendFormat(" AND KodeMasuk like 'persen{0}persen'", txtKodeMasuk.Text);
            if (!string.IsNullOrEmpty(txtKeterangan.Text))
                sb.AppendFormat(" AND [Description] like 'persen{0}persen'", txtKeterangan.Text);
            if (!string.IsNullOrEmpty(txtPallet.Text))
                sb.AppendFormat(" AND KodePallet like 'persen{0}persen'", txtPallet.Text);
            if (!string.IsNullOrEmpty(txtNoBundle.Text))
                sb.AppendFormat(" AND NoBundle ={0}", txtNoBundle.Text);
            if (!string.IsNullOrEmpty(txtPanjang.Text))
                sb.AppendFormat(" AND Panjang >={0} AND Panjang <={1}", txtPanjang.Text, txtPanjang2.Text);
            if (!string.IsNullOrEmpty(txtLebar.Text))
                sb.AppendFormat(" AND Lebar >={0} AND Lebar <={1}", txtLebar.Text, txtLebar2.Text);
            if (!string.IsNullOrEmpty(txtTinggi.Text))
                sb.AppendFormat(" AND Sheet >={0} AND Sheet <={1}", txtTinggi.Text, txtTinggi2.Text);
            if (!string.IsNullOrEmpty(txtAmount.Text))
                sb.AppendFormat(" AND Amount =persen{0}persen", txtAmount.Text);
            if (!string.IsNullOrEmpty(txtHpp.Text))
                sb.AppendFormat(" AND UnitPrice =persen{0}persen", txtHpp.Text);

            if (cboItem.Items.Cast<ListItem>().Any(item => item.Selected && !string.IsNullOrEmpty(item.Value)))
                sb.Append(" AND ItemId in (");

            foreach (ListItem item in cboItem.Items.Cast<ListItem>().Where(x => x.Selected && !string.IsNullOrEmpty(x.Value)))
                sb.AppendFormat("'{0}',", item.Value);

            if (cboItem.Items.Cast<ListItem>().Any(item => item.Selected && !string.IsNullOrEmpty(item.Value)))
            {
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
            }



            if (cboGroup.Items.Cast<ListItem>().Any(item => item.Selected && !string.IsNullOrEmpty(item.Value)))
                sb.Append(" AND GroupID in (");
            foreach (var group in cboGroup.Items.Cast<ListItem>().Where(x => x.Selected && !string.IsNullOrEmpty(x.Value)))
                sb.AppendFormat("'{0}',", group.Value);
            if (cboGroup.Items.Cast<ListItem>().Any(item => item.Selected && !string.IsNullOrEmpty(item.Value)))
            {
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
            }

            if (cboSubGroup.Items.Cast<ListItem>().Any(item => item.Selected && !string.IsNullOrEmpty(item.Value)))
                sb.Append(" AND SubGroupID in (");
            foreach (var group in cboSubGroup.Items.Cast<ListItem>().Where(x => x.Selected && !string.IsNullOrEmpty(x.Value)))
                sb.AppendFormat("'{0}',", group.Value);
            if (cboSubGroup.Items.Cast<ListItem>().Any(item => item.Selected && !string.IsNullOrEmpty(item.Value)))
            {
                sb.Remove(sb.Length - 1, 1);
                sb.Append(")");
            }

            sb.Append(" ORDER BY SubGroupID,ItemId  ");

            var query = sb.ToString();
            if (source == null)
            {
                source = dataAccess.GetDataListDataTable(query.Replace("tambah", "+").Replace("persen", "%"));
                Cache["dt"] = source;
            }

            customerReport = new ReportDocument();
            string reportPath = string.Empty;

            if (rblTipe.SelectedValue == "Summary")
                reportPath = Server.MapPath("~/Report/SummaryKartuStok.rpt");
            else
              reportPath = Server.MapPath("~/Report/LapMutasiStokNew.rpt");

            customerReport.Load(reportPath);
            customerReport.SetDataSource(source);
            SetParameter();
            CrystalReportViewer1.ReportSource = customerReport;
        }

        private void SetParameter()
        {
            var param = new ParameterDiscreteValue();
            param.Value = txtTgl1.Text;
            var paramValue = new ParameterValues();
            paramValue.Add(param);
            customerReport.DataDefinition.ParameterFields["Tanggal"].ApplyCurrentValues(paramValue);

            var param2 = new ParameterDiscreteValue();
            param2.Value = string.IsNullOrEmpty(cboWarehouse.SelectedValue) ? "All" : cboWarehouse.SelectedValue;
            var param2Value = new ParameterValues();
            param2Value.Add(param2);
            customerReport.DataDefinition.ParameterFields["Warehouse"].ApplyCurrentValues(param2Value);


             if (rblTipe.SelectedValue == "Summary")
             {
                 var param3 = new ParameterDiscreteValue();
                 param3.Value = rblGroup.SelectedValue;
                 var param3Value = new ParameterValues();
                 param3Value.Add(param3);
                 customerReport.DataDefinition.ParameterFields["group"].ApplyCurrentValues(param3Value);
             }
          

        }

        protected override void OnUnload(EventArgs e)
        {
            base.OnUnload(e);
            this.Unload += Page_Unload;
        }

        void Page_Unload(object sender, EventArgs e)
        {
            if (customerReport != null)
            {
                customerReport.Clone();
                customerReport.Dispose();
            }
        }

        protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args)
        {
             DateTime result;
             args.IsValid = DateTime.TryParse(txtTgl1.Text, new System.Globalization.CultureInfo("id-ID"),DateTimeStyles.None, out result);
        }
    }
}